Deleting Records

Article
10/23/2006

You delete records by marking them for deletion, then removing the deleted records. Until you remove the records that are flagged for deletion, they are still on disk and can be unmarked and restored. This section describes how to mark, unmark, and remove records from your table.

Marking Records for Deletion

You can mark records for deletion through the interface or with the DELETE - SQL command.

To mark a record for deletion

In a Browse window, click the deletion marker to flag the record.

-or-
From the Table menu, choose Delete Records.

-or-
Use the DELETE - SQL command.

You can use the DELETE - SQL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be marked for deletion. For example, the following code marks for deletion all product records with 'T' in the Discontinu field:

USE products
DELETE FROM products WHERE discontinu = .T.
BROWSE

You can also select a group of records for deletion by setting conditions in the Delete dialog box. Choose Delete Records from the Table menu to enter deletion criteria.

Choose the Scope button to set the range of records to be deleted.

If you can describe a set of records you want to delete, you can build an expression based on that description. Choose the FOR button to bring up the Expression Builder so you can build an expression. For example, using the expression FOR Country = 'UK' selects all the records from the UK and marks them for deletion.

Records you mark for deletion are not physically removed from the table until you issue a PACK command. When you view the table in the Browse window, you'll see that the deletion marker is flagged for each deleted record, but the record is still visible in the table, if SET DELETED is set to off. If SET DELETED is set to on, the records marked for deletion are excluded from the Browse window.

The setting of the SET DELETED command also affects whether records marked for deletion are accessible by commands that operate on records.

Retrieving Records Marked for Deletion

You can unmark records that were marked for deletion with the RECALL command. The RECALL command can recover records only if you have not issued a PACK or ZAP command, which physically deletes the records from the table.

To unmark a record marked for deletion

In a Browse window, click the deletion marker to unmark the record.

-or-
From the Table menu, choose Recall Records.

-or-
Use the RECALL command.

You can use the RECALL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be unmarked for deletion. For example, the following code unmarks for deletion all product records with 'T' in the discontinu field:

USE products 
RECALL FOR discontinu = .T.
BROWSE

When you view the table in the Browse window, you'll see that the deletion marker is not flagged for the records.

Removing Records Marked for Deletion

After you've marked records for deletion, you can permanently remove them from disk through the interface or the language.

To remove records marked for deletion from disk

In a Browse window, choose Remove Deleted Records from the Table menu.
Choose Yes in response to the prompt that asks if you want to pack the table.

-or-

Use the PACK command.

The PACK command has two clauses: MEMO and DBF. When you issue PACK without the MEMO or DBF clauses, records in both the table file and the associated memo file are removed. Make sure you have exclusive access to the table. For example, the following code removes records marked for deletion:

USE customer EXCLUSIVE
PACK

To delete records in the table file only and leave the memo file untouched, use PACK DBF.

Conserving Space

Information in table memo fields is stored in an associated memo file with the same name as the table and an .fpt extension. If you want to remove unused space from the memo file without removing records that are marked for deletion, issue the PACK command with the MEMO clause. Make sure you have exclusive access to the table.

Removing All Records from a Table

If you want to remove all records from a table, and leave just the table structure, you can use the ZAP command. Issuing ZAP is equivalent to issuing DELETE ALL followed by PACK, but ZAP is much faster. Make sure you have exclusive access to the table.

Caution Records zapped from the current table cannot be recalled.